{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "class GradeAnalysis:\n",
    "    def __init__(self, file_path):\n",
    "        # 导入数据\n",
    "        self.data = pd.read_excel(file_path, engine='openpyxl')\n",
    "        self.students = self._process_data()\n",
    "\n",
    "    def _process_data(self):\n",
    "        \"\"\"\n",
    "        处理 Excel 数据并存储为字典格式\n",
    "        \"\"\"\n",
    "        students = {}\n",
    "        for _, row in self.data.iterrows():\n",
    "            student_id = str(row['ID'])  # 学生ID\n",
    "            students[student_id] = {\n",
    "                \"name\": row['姓名'],  # 学生姓名\n",
    "                \"scores\": row[2:-1].tolist(),  # 各题得分（假设题目列在第3列到倒数第2列）\n",
    "                \"total\": row['总分']  # 总分\n",
    "            }\n",
    "        return students\n",
    "\n",
    "    def grade_distribution(self):\n",
    "        \"\"\"\n",
    "        统计成绩分布：成绩区间人数\n",
    "        \"\"\"\n",
    "        bins = [0, 60, 70, 80, 90, 100]  # 分数区间\n",
    "        labels = ['不及格', '及格', '中等', '良好', '优秀']\n",
    "        self.data['分数区间'] = pd.cut(self.data['总分'], bins=bins, labels=labels, right=False)\n",
    "        distribution = self.data['分数区间'].value_counts().sort_index()\n",
    "        return distribution\n",
    "\n",
    "    def pass_rate_and_average(self):\n",
    "        \"\"\"\n",
    "        统计及格率和平均分\n",
    "        \"\"\"\n",
    "        total_students = len(self.data)\n",
    "        passed_students = len(self.data[self.data['总分'] >= 60])\n",
    "        pass_rate = passed_students / total_students * 100\n",
    "        average_score = self.data['总分'].mean()\n",
    "        return pass_rate, average_score\n",
    "\n",
    "    def single_question_analysis(self):\n",
    "        \"\"\"\n",
    "        单题得分分析：统计每道题的平均分、最高分、最低分\n",
    "        \"\"\"\n",
    "        question_columns = self.data.columns[2:-1]  # 假设题目列在第3列到倒数第2列\n",
    "        analysis = {}\n",
    "        for question in question_columns:\n",
    "            average_score = self.data[question].mean()\n",
    "            max_score = self.data[question].max()\n",
    "            min_score = self.data[question].min()\n",
    "            analysis[question] = {\n",
    "                \"average\": average_score,\n",
    "                \"max\": max_score,\n",
    "                \"min\": min_score\n",
    "            }\n",
    "        return analysis\n",
    "\n",
    "    def rank_students(self):\n",
    "        \"\"\"\n",
    "        按总分排名，并分类统计\n",
    "        \"\"\"\n",
    "        self.data['排名'] = self.data['总分'].rank(ascending=False, method='min')  # 按总分排名\n",
    "        bins = [0, 60, 70, 80, 90, 100]  # 分数区间\n",
    "        labels = ['不及格', '及格', '中等', '良好', '优秀']\n",
    "        self.data['分类'] = pd.cut(self.data['总分'], bins=bins, labels=labels, right=False)\n",
    "        rank_data = self.data.sort_values(by='总分', ascending=False)[['ID', '姓名', '总分', '排名', '分类']]\n",
    "        return rank_data\n",
    "\n",
    "# 示例：使用数据分析功能\n",
    "file_path = r\"D:\\other\\1st\\shuju.xlsx\"  # 替换为实际 Excel 文件路径\n",
    "analysis = GradeAnalysis(file_path)\n",
    "\n",
    "# 1. 成绩分布统计\n",
    "print(\"成绩分布统计：\")\n",
    "print(analysis.grade_distribution())\n",
    "\n",
    "# 2. 统计及格率和平均分\n",
    "pass_rate, average_score = analysis.pass_rate_and_average()\n",
    "print(f\"\\n及格率：{pass_rate:.2f}%\")\n",
    "print(f\"平均分：{average_score:.2f}\")\n",
    "\n",
    "# 3. 单题得分分析\n",
    "print(\"\\n单题得分分析：\")\n",
    "single_question_stats = analysis.single_question_analysis()\n",
    "for question, stats in single_question_stats.items():\n",
    "    print(f\"{question} - 平均分: {stats['average']:.2f}, 最高分: {stats['max']}, 最低分: {stats['min']}\")\n",
    "\n",
    "# 4. 学生成绩排名\n",
    "print(\"\\n学生成绩排名：\")\n",
    "print(analysis.rank_students())"
   ]
  }
 ],
 "metadata": {
  "language_info": {
   "name": "python"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
